﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.IO.Compression;
using System.Data.SqlClient;
using System.Xml;
using System.Collections;

namespace SharePointDatabaseExplorer
{
    public partial class frmMain : Form
    {
        public string sGUID;
        public string sListName;
        public string sSiteGUID;
        public Hashtable htMetadata = new Hashtable();
        TreeNode parentNode = null;

        public frmMain()
        {
            InitializeComponent();
        }

        private void configurationToolStripMenuItem_Click(object sender, EventArgs e)
        {
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            SetScreenResolution();
            tsDatabase.Text = "Database Name: ";
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            //tvSharePointObjects.ImageList = ImageList;
            PopulateTree();
        }

        private void PopulateTree()
        {
            try
            {
                tvSharePointObjects.Nodes.Clear();
                
                DataSet ds = Database.RunQuery("select w.Title,w.Id, w.ParentWebId from webs w where w.ParentWebID is null");

                foreach (DataRow dr in ds.Tables[0].Rows)
                {

                    parentNode = tvSharePointObjects.Nodes.Add(dr["Title"].ToString() + " | " + dr["Id"].ToString());
                    Font f = GetFont();
                    parentNode.NodeFont = f;
                    
                    CreateNode(parentNode);
                    PopulateTreeView(dr["Id"].ToString(), parentNode);
                }

                tvSharePointObjects.Nodes[0].ImageIndex = 0;
                tvSharePointObjects.Nodes[0].Expand();
            }
            catch (System.ArgumentException ae)
            {
                MessageBox.Show("Unable to refresh the list.", "Unable to refresh list", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
            catch (Exception exp)
            {
                MessageBox.Show("Error filling root nodes.  Set the connection string information in Options -> Settings." + exp.Message, "Error filling root node", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void PopulateTreeView(string parentId, TreeNode parentNode)
        {
            String SQLChild = "select w.Title,w.Id, w.ParentWebId from webs w WHERE ParentWebId = '" + parentId + "'";
            //SqlDataAdapter dachildmnuc = new SqlDataAdapter(Seqchildc, conn);
            //DataTable dtchildc = new DataTable();

            DataSet ds = Database.RunQuery(SQLChild);

            TreeNode childNode;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                if (parentNode == null)
                {
                    childNode = tvSharePointObjects.Nodes.Add(dr["Title"].ToString() + " | " + dr["Id"].ToString());
                    childNode.ImageIndex = 1;
                    //parentNode.ExpandAll();
                }
                else
                {
                    childNode = parentNode.Nodes.Add(dr["Title"].ToString() + " | " + dr["Id"].ToString());
                    Font f = GetFont();
                    childNode.NodeFont = f;
                    childNode.ImageIndex = 1;
                }
                CreateNode(childNode);
                PopulateTreeView(dr["Id"].ToString(), childNode);
            }

        }

        private System.Drawing.Font GetFont()
        {
            Font f = new Font("Arial", 10, FontStyle.Bold);
            return f;
        } 


        private void PopulateTreeOLD()
        {
            try
            {
                dgSearch.Rows.Clear();
                dgResults.Rows.Clear();
                tvSharePointObjects.Nodes.Clear();

                DataSet ds = Database.RunQuery("select w.Title,w.Id from webs w order by parentwebid");
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    TreeNode rootId = new TreeNode(ds.Tables[0].Rows[i]["Id"].ToString());
                    TreeNode root = new TreeNode(ds.Tables[0].Rows[i]["Title"].ToString() + " | " + rootId.Text);
                    root.ExpandAll();

                    tvSharePointObjects.Nodes.Add(root);
                    CreateNode(rootId);
                }
            }
            catch (System.ArgumentException ae)
            {
                MessageBox.Show("Unable to refresh the list.", "Unable to refresh list", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
            catch (Exception exp)
            {
                MessageBox.Show("Error filling root nodes.  Set the connection string information in Options -> Settings." + exp.Message, "Error filling root node", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        void CreateNode(TreeNode node)
        {
            try
            {
                sSiteGUID = string.Empty;
                int iPipe = node.Text.IndexOf("|") + 1;
                int iLen = node.Text.Length;
                sSiteGUID = node.Text.Substring(iPipe + 1);

                DataSet ds = Database.RunQuery("select tp_title, tp_WebId, tp_ID, tp_ServerTemplate from alllists where tp_WebId = '" + sSiteGUID + "' ORDER BY tp_Title");
                if (ds.Tables[0].Rows.Count == 0)
                {
                    return;
                }
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    TreeNode tnodeId = new TreeNode(ds.Tables[0].Rows[i]["tp_ID"].ToString());
                    TreeNode tnode = new TreeNode(ds.Tables[0].Rows[i]["tp_Title"].ToString() + " | " + tnodeId.Text);
                    tnode.ImageIndex = GetImageIndex(ds.Tables[0].Rows[i]["tp_ServerTemplate"].ToString());
                    tnode.SelectedImageIndex = GetImageIndex(ds.Tables[0].Rows[i]["tp_ServerTemplate"].ToString());
                    tnode.ExpandAll();

                    node.Nodes.Add(tnode);
                    
                }
            }
            catch (Exception exp)
            {
                MessageBox.Show("Error creating node. Set the connection string information in Options -> Settings." + exp.Message, "Error building nodes",MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private int GetImageIndex(string TemplateID)
        {
            int iTemplateID;
            switch (TemplateID)
            {
                case "100":
                    iTemplateID = 7;
                    break;
                case "101":
                    iTemplateID = 5;
                    break;
                case "102":
                    iTemplateID = 10;
                    break;
                case "103":
                    iTemplateID = 8;
                    break;
                case "104":
                    iTemplateID = 2;
                    break;
                case "105":
                    iTemplateID = 3;
                    break;
                case "106":
                    iTemplateID = 6;
                    break;
                case "107":
                    iTemplateID = 11;
                    break;
                case "108":
                    iTemplateID = 4;
                    break;
                case "109":
                    iTemplateID = 9;
                    break;
                default:
                    iTemplateID = 7;
                    break;
            }
            return iTemplateID;
        }

        private void databaseConnectionToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmDatabaseConnection DatabaseConnection = new frmDatabaseConnection();
            DatabaseConnection.ShowDialog();
            tsDatabase.Text = "Database Name: " + Database._DatabaseName;

            //Use this link to dislay the sites in a hierarchy.
            //http://www.c-sharpcorner.com/Blogs/11439/database-driven-treeview-in-windows-forms-and-C-Sharp.aspx
            PopulateTree();
        }

        private void closeToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.Close();
        }

       private void Main_Resize(object sender, EventArgs e)
        {
            SetScreenResolution();
        }

        private void SetScreenResolution()
        {
            dgSearch.Width = this.Width - 320;
            dgResults.Width = this.Width - 320;
            dgResults.Height = this.Height - 280;
            tvSharePointObjects.Height = this.Height - 118;
            btnSearch.Left = this.Width - 100;
            this.WindowState = FormWindowState.Maximized;
        }

        private void tvSharePointObjects_NodeMouseClick(object sender, TreeNodeMouseClickEventArgs e)
        {
            try
            {
                dgSearch.Rows.Clear();
                htMetadata.Clear();

                int iPipe = e.Node.Text.IndexOf("|") + 1;
                int iLen = e.Node.Text.Length;
                sGUID = e.Node.Text.Substring(iPipe + 1);
                sListName = e.Node.Text.Substring(0, iPipe -1);

                PopulateSearchGrid(0);
            }
            catch
            {
                //MessageBox.Show("Select a list or library to search","Select Object",MessageBoxButtons.OK,MessageBoxIcon.Information);
            }
        }

        private void PopulateSearchGrid(int RowIndex)
        {
            DataGridViewComboBoxCell ComboColumn = (DataGridViewComboBoxCell)(dgSearch.Rows[RowIndex].Cells[0]);
            DataSet dsMetadata = Database.RunQuery("select tp_Fields from AllLists where tp_Id = '" + sGUID + "'");
            
            string uncompressedStringBuffer = String.Empty;

            string sMetadata = String.Empty;
            string sFields = string.Empty;
            //sMetadata = dsMetadata.Tables[0].Rows[0]["tp_Fields"].ToString();
            byte[] bMetadata = (byte[])dsMetadata.Tables[0].Rows[0]["tp_Fields"];

            sFields = getXmlFromTpFields(bMetadata);

            XmlDocument xmlDocument = new XmlDocument();

            xmlDocument.LoadXml("<uncompressedData></uncompressedData>");

            bool isXmlDocument = false;

            try
            {
                xmlDocument.FirstChild.InnerXml = sFields;
                isXmlDocument = true;
            }
            catch (Exception exception)
            {
                isXmlDocument = false;
            }
            
            if (isXmlDocument)
            {
                using (MemoryStream xslMemoryStream = new MemoryStream())
                {
                    string sFieldName = "DisplayName";
                    string sType = string.Empty;
                    XmlNodeList xnList = xmlDocument.SelectNodes("//Field");
                    if (xnList.Count == 0)
                    {
                        xnList = xmlDocument.SelectNodes("//FieldRef");
                        sFieldName = "Name";
                    }
                    foreach (XmlNode xn in xnList)
                    {
                        try
                        {
                            string sDisplayName = xn.Attributes[sFieldName].InnerText.ToString();
                            string sColumnName = xn.Attributes["ColName"].InnerText.ToString();
                            try
                            {
                                sType = xn.Attributes["Type"].InnerText.ToString();
                            }
                            catch {}

                            if (!ComboColumn.Items.Contains(sDisplayName))
                            {
                                //Don't get the workflow status column because it comes out in unreadable characters.
                                if (sType != "WorkflowStatus")
                                {
                                    ComboColumn.Items.Add(sDisplayName);
                                    if (!htMetadata.Contains(Database.ParseColumn(sDisplayName)));
                                    {
                                        htMetadata.Add(Database.ParseColumn(sDisplayName.Replace("'","''")), sColumnName);
                                    }
                                }
                            }
                        }
                        catch { }
                    }
                    if (!htMetadata.Contains("Created"))
                    {
                        htMetadata.Add("Created", "tp_Created");
                    }
                    if (!htMetadata.Contains("Modified"))
                    {
                        htMetadata.Add("Modified", "tp_Modified");
                    }
                    ComboColumn.Sorted = true;
                }
            }
        }

        private static string getXmlFromTpFields(byte[] tpFields)
        {
            try
            {
                using (var memoryStream = new MemoryStream(tpFields))
                {
                    // ignore the first 14 bytes; I'm not sure why but it works!
                    for (var index = 0; index <= 13; index++)
                        memoryStream.ReadByte();

                    var deflateStream = new DeflateStream(memoryStream, CompressionMode.Decompress);

                    using (var destination = new MemoryStream())
                    {
                        deflateStream.CopyTo(destination);

                        var streamReader = new StreamReader(destination);
                        destination.Position = 0;
                        return streamReader.ReadToEnd();
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show("Error loading fields");
                return null;
            }
        }

        private void dgSearch_UserAddedRow(object sender, DataGridViewRowEventArgs e)
        {
            PopulateSearchGrid(e.Row.Index);
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.WaitCursor;
            try
            {
                StringBuilder sbColumns = new StringBuilder();
                string sWhere = string.Empty;
                string sAnd = " AND ";

                foreach (DictionaryEntry entry in htMetadata)
                {
                    string sDisplayName = entry.Key.ToString();
                    string sColumnName = entry.Value.ToString();
                    sbColumns.Append(sColumnName + " as '" + sDisplayName + "',");
                    //sbColumns.Append(sColumnName + ",");
                }

                sbColumns.Remove(sbColumns.Length - 1, 1);

                for (int i = 0; i < dgSearch.Rows.Count - 1; i++)
                {
                    DataGridViewRow row = dgSearch.Rows[i];
                    if (row.Cells["Metadata"].Value.ToString() != "")
                    {
                        sWhere += (htMetadata[row.Cells["Metadata"].Value] + " " + GetOperator(row.Cells["Operator"].Value.ToString()) + " " + FormatWhereValue(row.Cells["Value"].Value.ToString()));

                    }
                    if (row.Cells["Condition"].Value != null)
                    {
                        sWhere += (GetCondition(row.Cells["Condition"].Value.ToString()));
                    }
                }

                if (sWhere.Length > 0)
                {
                    sWhere = sAnd + sWhere;
                }

                StringBuilder sbWhere = new StringBuilder(sWhere);

                if (sWhere.EndsWith(" AND ") || sWhere.EndsWith("  OR "))
                {
                    sbWhere.Remove(sWhere.Length - 5, 5);

                }

                sbWhere.Replace("*", "%");

                string sSQL = "SELECT docs.LeafName as Name, tp_Id, " + sbColumns.ToString() + " FROM AllUserData ad, allDocs docs WHERE ad.tp_ListId = '" + sGUID + "' AND ad.tp_DocID = docs.id  and ad.tp_Version = (select max(tp_Version) from AllUserData ad1 where tp_ListID = '" + sGUID + "' and ad.tp_ID = ad1.tp_ID) " + sbWhere;
                DataSet dsResults = Database.RunQuery(sSQL);

                dgResults.AutoGenerateColumns = true;
                dgResults.DataSource = dsResults.Tables[0];
                toolStripStatusTotalRecords.Text = " | List Name: " + sListName + " |  Total Records: " + dsResults.Tables[0].Rows.Count.ToString();
                this.Cursor = Cursors.Default;
            }
            catch (Exception exp)
            {
                this.Cursor = Cursors.Default;
                MessageBox.Show("Something bad happened! " + exp.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private string GetOperator(string Operator)
        {
            string sReturn = string.Empty;
            switch(Operator)
            {
                case "Equal To":
                    sReturn = "=";
                    break;
                case "Contains":
                    sReturn = "LIKE";
                    break;
                case "Greater Than":
                    sReturn = ">";
                    break;
                case "Greater Than Equal To":
                    sReturn = ">=";
                    break;
                case "Less Than":
                    sReturn = "<";
                    break;
                case "Less Than Equal To":
                    sReturn = "<=";
                    break;
                case "Not Equal To":
                    sReturn = "<>";
                    break;
            }
            return sReturn;
        }

        private string GetCondition(string Condition)
        {
            string sReturn = string.Empty;

            switch (Condition)
            {
                case "And":
                    sReturn = " AND ";
                    break;
                case "Or":
                    sReturn = "  OR ";
                    break;
            }

            return sReturn;
        }

        private string FormatWhereValue(string Value)
        {
            string sReturn = string.Empty;
            sReturn = "'" + Value + "'";
            
            return sReturn;
        }

        private int FormatWhereValue(int Value)
        {
            return Value;
        }

        private void dgResults_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                ContextMenu m = new ContextMenu();
                m.MenuItems.Add(new MenuItem("View", new EventHandler(btnView_Click)));
                m.MenuItems.Add(new MenuItem("Downlaod", new EventHandler(btnExportFile_Click)));
                //m.MenuItems.Add(new MenuItem("Paste"));

                int currentMouseOverRow = dgResults.HitTest(e.X, e.Y).RowIndex;

                if (currentMouseOverRow >= 0)
                {
                    m.MenuItems.Add(new MenuItem(string.Format("Do something to row {0}", currentMouseOverRow.ToString())));
                }

                m.Show(dgResults, new Point(e.X, e.Y));

            }
        }

        private void btnExportFile_Click(object sender, EventArgs e)
        {
            ExportFile();
        }

        private void ExportFile()
        {
            if (dgResults.SelectedRows.Count > 0)
            {
                Boolean bSuccess = false;

                SharePoint objSharePoint = new SharePoint();

                foreach (DataGridViewRow row in dgResults.SelectedRows)
                {
                    string sID = row.Cells[0].Value.ToString();
                    objSharePoint.ID = Convert.ToInt16(sID);
                    objSharePoint.ListID = sGUID;
                    bSuccess = objSharePoint.ExportFile();
                }
                if (bSuccess)
                {
                    MessageBox.Show("Files successfully exported to " + Database._FileOutputPath, "File Exported", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("There are no files to export", "No Files Exported", MessageBoxButtons.OK, MessageBoxIcon.Information);

                }
            }
            else
            {
                MessageBox.Show("Select a file(s) to export", "Select File(s)", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }

        private void ViewFile()
        {
            try
            {
                SharePoint.FilePath = "";
                ExportFile();
                System.Diagnostics.Process.Start(SharePoint.FilePath);
            }
            catch (Exception exp)
            {
                MessageBox.Show("Select a file to view.", "Select File", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }

        private void btnView_Click(object sender, EventArgs e)
        {
            ViewFile();
        }

        private void btnExportToExcel_Click(object sender, EventArgs e)
        {
            if (dgResults.Rows.Count > 0)
            {
                int iPipe = tvSharePointObjects.SelectedNode.Text.IndexOf("|") - 1;
                int iLen = tvSharePointObjects.SelectedNode.Text.Length;
                string sListName = tvSharePointObjects.SelectedNode.Text.Substring(0, iPipe);
                string sExportFile = Database._FileOutputPath + @"\" + sListName + ".csv";
                SharePoint.ExportMetadataToCSV(dgResults, sExportFile);

                MessageBox.Show("Successfully exported the results to: " + sExportFile, "Results Exported", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("Select a list and retrieve the results to export", "No Results to Export", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }

        private void btnExportFile_MouseHover(object sender, EventArgs e)
        {
            ToolTip t = new ToolTip();
            t.Show("Export selected file.", btnExportFile);
        }

        private void btnView_MouseHover(object sender, EventArgs e)
        {
            ToolTip t = new ToolTip();
            t.Show("View selected file.", btnView);
        }

        private void btnExportToExcel_MouseHover(object sender, EventArgs e)
        {
            ToolTip t = new ToolTip();
            t.Show("Export metadata to Excel", btnExportToExcel);
        }

        private void refreshSharePointObjectsToolStripMenuItem_Click(object sender, EventArgs e)
        {
            PopulateTree();
        }

        private void btnVersions_Click(object sender, EventArgs e)
        {
            ViewVersions();
        }
        private void btnVersions_MouseHover(object sender, EventArgs e)
        {
            ToolTip t = new ToolTip();
            t.Show("View versions",btnVersions);
        }

        private void ViewVersions()
        {
            frmVersions fVersions = new frmVersions();
            fVersions.ListID = sGUID;
            fVersions.ListName = sListName;
            fVersions.SiteID = sSiteGUID;
            fVersions.MetadataFields = htMetadata;

            if (dgResults.SelectedRows.Count == 1)
            {
                foreach (DataGridViewRow row in dgResults.SelectedRows)
                {
                    string sID = row.Cells[1].Value.ToString();
                    fVersions.ID = Convert.ToInt16(sID);
                }
                fVersions.ShowDialog();
            }
            else
            {
                MessageBox.Show("Select a single row to view its versions", "Select a single row", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
            
        }

        private void dgResults_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {
            //MessageBox.Show (e.Context.ToString());
        }


    }
}
